package sg.edu.nus.iss.phoenix.schedule.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import sg.edu.nus.iss.phoenix.authenticate.entity.User;
import sg.edu.nus.iss.phoenix.core.dao.DBConstants;
import sg.edu.nus.iss.phoenix.core.exceptions.NotFoundException;
import sg.edu.nus.iss.phoenix.radioprogram.entity.RadioProgram;
import sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO;
import sg.edu.nus.iss.phoenix.schedule.entity.Schedule;
import sg.edu.nus.iss.phoenix.utils.PhoenixUtils;


/**
 * Schedule Data Access Object (DAO). This class contains all database
 * handling that is needed to permanently store and retrieve Schedule object
 * instances.
 * 
 * @author Low  Kheng Hian (A0092668N)
 * @author Nang Sai Hom (A0092617B)
 *  
 * @version 1.0 2012/9/27
 */


public class ScheduleDAOImpl implements ScheduleDAO {

	Connection connection;

	/*
	 * (non-javadoc)
	 * 
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO#createValueObject()
	 */
	@Override
	public Schedule createValueObject() {
		return new Schedule();
	}

	/*
	 * (non-javadoc)
	 * 
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO#getObject(int)
	 */
	@Override
	public Schedule getObject(int schedule_id) throws NotFoundException,
			SQLException {
		Schedule valueObject = createValueObject();
		valueObject.setScheduleId(schedule_id);
		load(valueObject);
		return valueObject;
	}

	/*
	 * (non-javadoc)
	 * 
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO#load(Schedule)
	 */
	@Override
	public Schedule load(Schedule valueObject) throws NotFoundException,
			SQLException {
		if (!PhoenixUtils
				.isNumeric(String.valueOf(valueObject.getScheduleId()))) {
			throw new NotFoundException("Can not select without Primary-Key!");
		}

		String sql = "SELECT * FROM `schedule` WHERE (`schedule_id` = ? ); ";
		PreparedStatement stmt = null;
		openConnection();
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, valueObject.getScheduleId());

			valueObject = singleQuery(stmt, valueObject);

			return valueObject;

		} finally {
			if (stmt != null)
				stmt.close();
			closeConnection();
		}
	}

	/*
	 * (non-javadoc)
	 * 
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO#loadAll()
	 */
	@Override
	public List<Schedule> loadAll() throws SQLException {
		String sql = "SELECT * FROM `schedule` ORDER BY `schedule_id` ASC; ";
		openConnection();
		List<Schedule> searchResults = listQuery(connection
				.prepareStatement(sql));
		closeConnection();
		return searchResults;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO#create(sg.edu.nus.iss
	 * .phoenix.schedule.entity.Schedule)
	 */
	@Override
	public void create(Schedule valueObject) throws SQLException {
		String sql = "";
		PreparedStatement stmt = null;
		try {
			openConnection();
			sql = "INSERT INTO `schedule` (`program_id`, `start_time`, `end_time`, `presenter_id`, `producer_id`) VALUES (?,?,?,?,?); ";
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, valueObject.getProgram().getProgram_id());
			stmt.setTimestamp(2, PhoenixUtils.convertToSQLTimeStamp(valueObject.getStartDate()));
			stmt.setTimestamp(3, PhoenixUtils.convertToSQLTimeStamp(valueObject.getEndDate()));
			stmt.setString(4, valueObject.getPresenter().getId());
			stmt.setString(5, valueObject.getProducer().getId());
			int rowcount = databaseUpdate(stmt);

			if (rowcount != 1) {
				// // System.out.println("PrimaryKey Error when updating DB!");
				throw new SQLException("PrimaryKey Error when updating DB!");
			}

		} finally {
			if (stmt != null)
				stmt.close();
			closeConnection();
		}

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO#save(sg.edu.nus.iss.phoenix
	 * .schedule.entity.Schedule)
	 */
	@Override
	public void save(Schedule valueObject) throws NotFoundException,
			SQLException {
		String sql = "UPDATE `schedule` SET `program_id` = ?, `start_time` = ?, `end_time` = ?, `presenter_id` = ? , `producer_id` = ? WHERE (`schedule_id` = ? ); ";
		PreparedStatement stmt = null;
		try {
			openConnection();
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, valueObject.getProgram().getProgram_id());
						
			// System.out.println(" start date " + valueObject.getStartDate());
			stmt.setTimestamp(2, PhoenixUtils.convertToSQLTimeStamp(valueObject.getStartDate()));
			stmt.setTimestamp(3, PhoenixUtils.convertToSQLTimeStamp(valueObject.getEndDate()));
			stmt.setString(4, valueObject.getPresenter().getId());
			stmt.setString(5, valueObject.getProducer().getId());
			stmt.setInt(6, valueObject.getScheduleId());

			int rowcount = databaseUpdate(stmt);
			if (rowcount == 0) {
				// // System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// // System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
			closeConnection();
		}

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO#delete(sg.edu.nus.iss
	 * .phoenix.schedule.entity.Schedule)
	 */
	@Override
	public void delete(Schedule valueObject) throws NotFoundException,
			SQLException {
		if (valueObject != null
				&& !PhoenixUtils.isNumeric(String.valueOf(valueObject
						.getScheduleId()))) {
			// // System.out.println("Can not delete without Primary-Key!");
			throw new NotFoundException("Can not delete without Primary-Key!");
		}

		String sql = "DELETE FROM `schedule` WHERE (`schedule_id` = ? ); ";
		PreparedStatement stmt = null;
		openConnection();
		try {
			stmt = connection.prepareStatement(sql);
			// System.out.println(valueObject.getScheduleId());
			stmt.setInt(1, valueObject.getScheduleId());

			int rowcount = databaseUpdate(stmt);
			if (rowcount == 0) {
				// // System.out.println("Object could not be deleted (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be deleted! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// // System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were deleted!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
			closeConnection();
		}

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO#deleteAll(java.sql.Connection
	 * )
	 */
	@Override
	public void deleteAll(Connection conn) throws SQLException {
		String sql = "DELETE FROM `schedule`";
		PreparedStatement stmt = null;
		openConnection();
		try {
			stmt = connection.prepareStatement(sql);
			int rowcount = databaseUpdate(stmt);
			// System.out.println("" + rowcount);
		} finally {
			if (stmt != null)
				stmt.close();
			closeConnection();

		}

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO#countAll()
	 */
	@Override
	public int countAll() throws SQLException {

		String sql = "SELECT count(*) FROM `schedule`";
		PreparedStatement stmt = null;
		ResultSet result = null;
		int allRows = 0;
		openConnection();
		try {
			stmt = connection.prepareStatement(sql);
			result = stmt.executeQuery();

			if (result.next())
				allRows = result.getInt(1);
		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
			closeConnection();
		}
		return allRows;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO#searchMatching(sg.edu
	 * .nus.iss.phoenix.schedule.entity.Schedule)
	 */
	@Override
	public List<Schedule> searchMatching(Schedule valueObject)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO#loadAll(java.util.Date)
	 */
	@Override
	public List<Schedule> loadAll(Date date) {
		openConnection();
		String sql = "SELECT * FROM `schedule` where DATE(start_time) = ?";
		List<Schedule> searchResults = new ArrayList<Schedule>();
		PreparedStatement stmt = null;
		try {
			openConnection();
			stmt = connection.prepareStatement(sql);
			java.sql.Date sqlDate = PhoenixUtils.convertToSQLDate(date);
			stmt.setDate(1, sqlDate);
//			stmt.setDate(2, sqlDate);

			searchResults = listQuery(stmt);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeConnection();
		}
		
		// System.out.println("record size" + searchResults.size());
		return searchResults;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO#copy(sg.edu.nus.iss.phoenix
	 * .schedule.entity.Schedule)
	 */

	/**
	 * databaseUpdate-method. This method is a helper method for internal use.
	 * It will execute all database handling that will change the information in
	 * tables. SELECT queries will not be executed here however. The return
	 * value indicates how many rows were affected. This method will also make
	 * sure that if cache is used, it will reset when data changes.
	 * 
	 * @param conn
	 *            This method requires working database connection.
	 * @param stmt
	 *            This parameter contains the SQL statement to be excuted.
	 */
	protected int databaseUpdate(PreparedStatement stmt) throws SQLException {
		
		int result = stmt.executeUpdate();
		
		
		return result;
	}

	/**
	 * databaseQuery-method. This method is a helper method for internal use. It
	 * will execute all database queries that will return only one row. The
	 * resultset will be converted to valueObject. If no rows were found,
	 * NotFoundException will be thrown.
	 * 
	 * @param stmt
	 *            This parameter contains the SQL statement to be excuted.
	 * @param valueObject
	 *            Class-instance where resulting data will be stored.
	 */
	protected Schedule singleQuery(PreparedStatement stmt, Schedule valueObject)
			throws NotFoundException, SQLException {

		ResultSet result = null;
		
		try {
			result = stmt.executeQuery();

			if (result.next()) {
				valueObject.setScheduleId(result.getInt("schedule_id"));

				Timestamp end = result.getTimestamp("end_time");
				Timestamp start = result.getTimestamp("start_time");
				
				if (end != null) {
					valueObject.setEndDate(new Date(end.getTime()));
				}else{
					valueObject.setEndDate(new Date());
				}
			
				if (start != null) {
					valueObject.setStartDate(new Date(start.getTime()));
				}else{
					valueObject.setStartDate(new Date());
				}

				User presenter = new User();
				presenter.setId(result.getString("presenter_id"));
				valueObject.setPresenter(presenter);

				User producer = new User();
				producer.setId(result.getString("producer_id"));
				valueObject.setProducer(producer);

				RadioProgram program = new RadioProgram();
				program.setProgram_id(result.getInt("program_id"));
				valueObject.setProgram(program);

				return valueObject;
			} else {
				throw new NotFoundException("Schedule Object Not Found!");
			}
		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
			
		}
	}

	/**
	 * databaseQuery-method. This method is a helper method for internal use. It
	 * will execute all database queries that will return multiple rows. The
	 * resultset will be converted to the List of valueObjects. If no rows were
	 * found, an empty List will be returned.
	 * 
	 * @param conn
	 *            This method requires working database connection.
	 * @param stmt
	 *            This parameter contains the SQL statement to be excuted.
	 */
	protected List<Schedule> listQuery(PreparedStatement stmt)
			throws SQLException {

		List<Schedule> searchResults = new ArrayList<Schedule>();
		ResultSet result = null;
		
		try {
			result = stmt.executeQuery();

			while (result.next()) {
				Schedule temp = createValueObject();

				temp.setScheduleId(result.getInt("schedule_id"));

		
				Timestamp end = result.getTimestamp("end_time");
				Timestamp start = result.getTimestamp("start_time");
				
				if (end != null) {
					temp.setEndDate(new Date(end.getTime()));
				}else{
					temp.setEndDate(new Date());
				}
			
				if (start != null) {
					temp.setStartDate(new Date(start.getTime()));
				}else{
					temp.setStartDate(new Date());
				}

				User presenter = new User();
				presenter.setId(result.getString("presenter_id"));
				temp.setPresenter(presenter);

				User producer = new User();
				producer.setId(result.getString("producer_id"));
				temp.setProducer(producer);

				RadioProgram program = new RadioProgram();
				program.setProgram_id(result.getInt("program_id"));
				temp.setProgram(program);

				searchResults.add(temp);
			}

		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
			
		}

		return (List<Schedule>) searchResults;
	}

	private void openConnection() {
		try {
			Class.forName(DBConstants.COM_MYSQL_JDBC_DRIVER);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			this.connection = DriverManager.getConnection(DBConstants.dbUrl,
					DBConstants.dbUserName, DBConstants.dbPassword);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	private void closeConnection() {
		try {
			this.connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
